#!/bin/bash
#auto increment is not required. See helpfuldocs/programs/sql/sqlite. By default, integer, primary key will also auto increment.
# ON DELETE CASCADE: when/if a template is deleted in base, remove the materials entries.
# check constraint to make sure ints are actually ints


#### RECIPE TEMPLATES
####
## Templates are a one to many table. 
## One template base to many templates_materials.
## ergo each row in template base has one to two dozen templates_materials rows.

sqlite3 -line recipe.db \
'create table templates_base (
Formula varchar(32), 
Quantity varchar(32), 
Customer varchar(32), 
Timedateunix bigint primary key, 
Notes varchar(6000)
);'

sqlite3 -line recipe.db \
'create table templates_materials (
Lbs smallint CHECK (CAST(Lbs||1 AS INTEGER) <> 0), 
Oz smallint CHECK (CAST(Oz||1 AS INTEGER) <> 0), 
Grams smallint CHECK (CAST(Grams||1 AS INTEGER) <> 0), 
Materials varchar(128), 
Internal varchar(32),
Timedateunix bigint,
FOREIGN KEY (Timedateunix)
       REFERENCES templates_base (Timedateunix) ON DELETE CASCADE
);'


# considered adding archive tables, but not now.
# will just make backups of db every day.
# not worth the trouble for archive tables yet

# customer is optional for templates_base. 



#### CUSTOMER LOTS
####
## One to many to many db relationship. 
## one customer in customer table 
## many lots per customer in lots_base 
## many rows of materials per lot in lots_materials 
## Note that lots tables are different from templates 


sqlite3 -line recipe.db \
'create table lots_customers (
CustomerID integer primary key,
Customer varchar(32)
);'

sqlite3 -line recipe.db \
'create table lots_base (
Formula varchar(32), 
Quantity varchar(32), 
CustomerID integer, 
Lotnumber varchar(32),
Timedateunix bigint primary key, 
Notes varchar(6000),
FOREIGN KEY (CustomerID)
  REFERENCES lots_customers (CustomerID)
);'

# timedateunix is set as the primary key for lots (and templates).  This is done so that when the data is saved, there is first
# two copies, then afterwards the old entry is deleted. If data loss occurs, the old data should still be valid (assuming
# it was entered right at least once) (somewhat more resilient). See save templates / save lot function.

sqlite3 -line recipe.db \
'create table lots_materials (
Lbs smallint CHECK (CAST(Lbs||1 AS INTEGER) <> 0), 
Oz smallint CHECK (CAST(Oz||1 AS INTEGER) <> 0), 
Grams smallint CHECK (CAST(Grams||1 AS INTEGER) <> 0), 
Materials varchar(128), 
Internal varchar(32),
Lotnumber varchar(32),
Timedateunix bigint,
FOREIGN KEY (Timedateunix)
       REFERENCES templates_base (Timedateunix) ON DELETE CASCADE
);'



